package com.xxc.hs.db;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.xxc.hs.bean.TraceInfo;

import java.util.ArrayList;
import java.util.List;

public class TraceInfoDAO {
    private TraceabilityDBHelper dbHelper;
    private SQLiteDatabase db;

    public TraceInfoDAO(Context context) {
        dbHelper = new TraceabilityDBHelper(context);
        db = dbHelper.getReadableDatabase();
    }

    // 查询溯源信息列表，支持分页和搜索（使用原始SQL避免LIMIT格式问题）
    @SuppressLint("Range")
    public List<TraceInfo> getTraceInfoList(String keyword, int page, int pageSize) {
        List<TraceInfo> traceInfoList = new ArrayList<>();
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM " + TraceabilityDBHelper.TABLE_TRACE);

        String selection = null;
        String[] selectionArgs = null;

        if (keyword != null && !keyword.isEmpty()) {
            selection = "WHERE name LIKE ? OR responsible LIKE ? OR location LIKE ?";
            selectionArgs = new String[]{"%"+keyword+"%", "%"+keyword+"%", "%"+keyword+"%"};
            sqlBuilder.append(" ").append(selection);
        }

        // 计算偏移量并添加分页参数
        int offset = (page - 1) * pageSize;
        sqlBuilder.append(" LIMIT ? OFFSET ?");

        // 合并参数
        String[] allArgs;
        if (selectionArgs != null) {
            allArgs = new String[selectionArgs.length + 2];
            System.arraycopy(selectionArgs, 0, allArgs, 0, selectionArgs.length);
            allArgs[allArgs.length - 2] = String.valueOf(pageSize);
            allArgs[allArgs.length - 1] = String.valueOf(offset);
        } else {
            allArgs = new String[]{String.valueOf(pageSize), String.valueOf(offset)};
        }

        // 执行原始SQL查询
        Cursor cursor = db.rawQuery(sqlBuilder.toString(), allArgs);

        if (cursor != null && cursor.moveToFirst()) {
            do {
                TraceInfo traceInfo = new TraceInfo();
                traceInfo.setId(cursor.getInt(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_ID)));
                traceInfo.setName(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_NAME)));
                traceInfo.setPhone(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_PHONE)));
                traceInfo.setResponsible(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_RESPONSIBLE)));
                traceInfo.setLocation(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_LOCATION)));
                traceInfo.setImagePath(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_IMAGE_PATH)));
                traceInfo.setQrCode(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_QR_CODE)));
                traceInfo.setTraceInfo(cursor.getString(cursor.getColumnIndex(TraceabilityDBHelper.COLUMN_TRACE_INFO)));

                traceInfoList.add(traceInfo);
            } while (cursor.moveToNext());
        }

        if (cursor != null) {
            cursor.close();
        }
        // 不在DAO中关闭数据库，由调用层管理
        // db.close();
        return traceInfoList;
    }

    // 查询总记录数（使用原始SQL）
    public int getTotalCount(String keyword) {
        int totalCount = 0;
        SQLiteDatabase db = dbHelper.getReadableDatabase();
        StringBuilder sqlBuilder = new StringBuilder("SELECT COUNT(*) FROM " + TraceabilityDBHelper.TABLE_TRACE);

        String selection = null;
        String[] selectionArgs = null;

        if (keyword != null && !keyword.isEmpty()) {
            selection = "WHERE name LIKE ? OR responsible LIKE ? OR location LIKE ?";
            selectionArgs = new String[]{"%"+keyword+"%", "%"+keyword+"%", "%"+keyword+"%"};
            sqlBuilder.append(" ").append(selection);
        }

        Cursor cursor = db.rawQuery(sqlBuilder.toString(), selectionArgs);

        if (cursor != null && cursor.moveToFirst()) {
            totalCount = cursor.getInt(0);
        }

        if (cursor != null) {
            cursor.close();
        }
        // db.close();
        return totalCount;
    }

    // 释放资源方法
    public void releaseResources() {
        if (db != null && db.isOpen()) {
            db.close();
            db = null;
        }

        if (dbHelper != null) {
            dbHelper = null;
        }
    }
}